import os
import json
import pymysql
from dotenv import load_dotenv
from typing import Dict, Optional
from datetime import date, datetime

# 加载环境变量
load_dotenv()


class DatabaseTool:
    """数据库查询工具类 - PyMySQL 实现（修复日期序列化问题）"""

    def __init__(self):
        # 从环境变量获取数据库配置
        self.db_config = {
            "host": os.getenv("DB_HOST", "localhost"),
            "port": int(os.getenv("DB_PORT", 3306)),
            "database": os.getenv("DB_NAME", "aiinterviewerdb"),
            "user": os.getenv("DB_USER", "root"),
            "password": os.getenv("DB_PASSWORD", "easyzhp123"),
            "charset": "utf8mb4",
            "cursorclass": pymysql.cursors.DictCursor  # 返回字典格式结果
        }

    def get_connection(self):
        """创建并返回数据库连接"""
        return pymysql.connect(**self.db_config)

    def get_candidate_info(self, mobile: str) -> Dict:
        """获取候选人信息"""
        sql = """
            SELECT 
                ir.job_name, ir.result, ir.take_time,
                c.real_name, c.mobile, c.email, c.birthday, c.city
            FROM interview_record ir
            JOIN candidate c ON ir.candidate_id = c.id
            WHERE c.mobile = %s
        """

        try:
            connection = self.get_connection()
            with connection:
                with connection.cursor() as cursor:
                    cursor.execute(sql, (mobile,))
                    result = cursor.fetchone()

                    if result:
                        # 转换日期对象为字符串
                        return self.convert_dates_to_strings(result)
                    else:
                        return {"error": "未找到候选人信息"}
        except pymysql.Error as e:
            return {"error": f"数据库查询失败: {str(e)}"}
        except Exception as e:
            return {"error": f"未知错误: {str(e)}"}

    def convert_dates_to_strings(self, data: dict) -> dict:
        """将字典中的日期对象转换为字符串"""
        converted = {}
        for key, value in data.items():
            if isinstance(value, (date, datetime)):
                converted[key] = value.isoformat()
            else:
                converted[key] = value
        return converted

    def format_candidate_info(self, candidate_info: Dict) -> str:
        """格式化候选人信息为自然语言描述"""
        if "error" in candidate_info:
            return candidate_info["error"]

        # 处理可能为None的字段
        def safe_get(key, default="无"):
            value = candidate_info.get(key)
            return value if value is not None else default

        return (
            f"候选人姓名: {safe_get('real_name')}\n"
            f"职位: {safe_get('job_name')}\n"
            f"上次面试结果: {safe_get('result')}\n"
            f"面试时长: {safe_get('take_time')}分钟\n"
            f"手机: {safe_get('mobile')}\n"
            f"邮箱: {safe_get('email')}\n"
            f"出生日期: {safe_get('birthday')}\n"
            f"所在城市: {safe_get('city')}\n"
        )

    def get_candidate_info_formatted(self, candidate_id: str) -> str:
        """一步获取格式化后的候选人信息"""
        info = self.get_candidate_info(candidate_id)
        return self.format_candidate_info(info)


# 使用示例
if __name__ == "__main__":
    # 测试前设置环境变量
    # os.environ["DB_HOST"] = "localhost"
    # os.environ["DB_PORT"] = "3306"
    # os.environ["DB_NAME"] = "aiinterviewerdb"
    # os.environ["DB_USER"] = "root"
    # os.environ["DB_PASSWORD"] = "easyzhp123"

    db_tool = DatabaseTool()
    mobile = "13917748763"

    info = db_tool.get_candidate_info(mobile)
    print("原始数据库结果:")

    # 安全地打印结果，避免序列化问题
    # if isinstance(info, dict):
    #     for key, value in info.items():
    #         print(f"{key}: {value}")
    # else:
    #     print(info['job_name'])

    print(info['job_name'])
    print(info['city'])

    # formatted = db_tool.format_candidate_info(info)
    # print("\n格式化后的候选人信息:")
    # print(formatted)
    #
    # # 一步获取格式化信息
    # print("\n一步获取格式化信息:")
    # print(db_tool.get_candidate_info_formatted(candidate_id))